package dataAccess;
import epam.car.rent.*;
import epam.car.rent.exceptions.*;


import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.Properties;

import org.h2.jdbcx.JdbcConnectionPool;


public class CarRentDaoImpl implements CarRentDao {
	
	private JdbcConnectionPool cp;
	
	private static Connection conn = null;
	private static Statement myStat = null;
	private static ResultSet resultSet = null;
    private static PreparedStatement prepStat = null;
	
    protected Connection getConnection() throws SQLException {
        Connection connection = cp.getConnection();
        return connection;
    }
    
    protected JdbcConnectionPool createPool() throws SQLException {
    	Properties dbProps = new Properties();
    	try {
    		dbProps.load(DbWriter.class.getResourceAsStream("/properties/db.properties"));
            Class.forName("org.h2.Driver");
        } catch (ClassNotFoundException e) {
            throw new SQLException(e);
        } catch(IOException e){
        	e.printStackTrace();
        }
//        System.out.println(dbProps.getProperty("url"));
        return JdbcConnectionPool.create(dbProps.getProperty("url"), "sa", ""); 
//        return JdbcConnectionPool.create("jdbc:h2:c:\\books\\h2\\databases\\test", "sa", "");
    }
    
    public void start() throws SQLException {
    	cp = createPool();
        cp.setMaxConnections(20);
    }

    public void stop() throws SQLException {
        try {
			cp.dispose();
		} catch (NullPointerException e) {
			//e.printStackTrace();
		}
    }
    
//	public int create(Management item) {
//        switch (Entities.valueOf(item.getClass().toString().replaceAll("^.+\\W", "").toUpperCase())) {
//		case CLIENT:
//			createClient(item);
//			break;
//		case CAR:
//			createCar(item);
//			break;
//		case MANAGER:
//			createManager(item);
//			break;
//		case ORDER:
//			createOrder(item);
//			break;
//		default:
//			break;
//		}
//		return 0;
//	}

	public void createOrder(Management item) {
		try {
			conn = getConnection();
			prepStat = conn.prepareStatement("INSERT INTO ORDERS VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
			prepStat.setLong(1, item.getId());
			prepStat.setString(2, item.getClient().getPassportId());
			prepStat.setLong(3, item.getCar().getId());
			prepStat.setString(4, item.getManager().getName());
			prepStat.setString(5, String.valueOf(item.getState()));
			if(item.getOrderExpires()!=null){
				prepStat.setString(6, item.getOrderExpires());
			} else{
				prepStat.setString(6, null);
			}
			prepStat.setString(7, item.getOrderCreated());
			prepStat.setString(8, item.getRejectReason());
			prepStat.executeUpdate();
		} catch (org.h2.jdbc.JdbcSQLException e){ 
			System.out.println("An order with ID="+item.getId()+" already exists.\n" +
					"You should use \"Update\" command instead of \"Create\".");
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
		    try {
				prepStat.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public void createManager(Management item) {
		try {
			conn = getConnection();
			prepStat = conn.prepareStatement("INSERT INTO MANAGERS VALUES (?, ?)");
			prepStat.setString(1, item.getName());
			prepStat.setString(2, item.getPassword());
			prepStat.executeUpdate();
		} catch (org.h2.jdbc.JdbcSQLException e){ 
			System.out.println("A manager with name \""+item.getName()+"\" already exists.\n" +
					"You should use \"Update\" command instead of \"Create\".");
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
		    try {
				prepStat.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public void createCar(Management item) {
		try {
			conn = getConnection();
			prepStat = conn.prepareStatement("INSERT INTO CARS VALUES (?, ?, ?, ?, ?, ?)");
			prepStat.setLong(1, item.getId());
			if (item.getRentBy()!=null){
				prepStat.setString(2, item.getRentBy().getPassportId());	
			}else{
				prepStat.setString(2, null);
			}
			prepStat.setString(3, item.getModel());
			prepStat.setDouble(4, item.getPrice());
			prepStat.setString(5, String.valueOf(item.getCarType()));
			prepStat.setString(6, String.valueOf(item.getDamages()));
			prepStat.executeUpdate();
		} catch (org.h2.jdbc.JdbcSQLException e){ 
			System.out.println("A car with ID="+item.getId()+" already exists.\n" +
					"You should use \"Update\" command instead of \"Create\".");
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
		    try {
				prepStat.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public void createClient(Management item) {
		try {
			conn = getConnection();
			prepStat = conn.prepareStatement("INSERT INTO CLIENTS VALUES (?, ?, ?, ?, ?)");
			prepStat.setString(1, item.getPassportId());
			prepStat.setString(2, item.getName());
			prepStat.setFloat(3, item.getMoney());
			prepStat.setString(4, item.getPassword());
			prepStat.setString(5, item.getEmail());
			prepStat.executeUpdate();
		} catch (org.h2.jdbc.JdbcSQLException e){ 
			System.out.println("A client with ID="+item.getPassportId()+" already exists.\n" +
					"You should use \"Update\" command instead of \"Create\".");
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
		    try {
				prepStat.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public int updateClient(Client c) {
		try {
			conn = getConnection();
			prepStat = conn
					.prepareStatement("UPDATE CLIENTS SET CLIENT_PID=?, "
							+ "CLIENT_NAME=?, " + "CLIENT_MONEY=?, "
							+ "CLIENT_PASSWORD=?, " + "CLIENT_EMAIL=? "
							+ "WHERE CLIENT_PID=?;");
			prepStat.setString(1, c.getPassportId());
			prepStat.setString(2, c.getName());
			prepStat.setFloat(3, c.getMoney());
			prepStat.setString(4, c.getPassword());
			prepStat.setString(5, c.getEmail());
			prepStat.setString(6, c.getPassportId());
			return prepStat.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				prepStat.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return -1;
	}
	
	public int updateOrder(Order o) {
		try {
			conn = getConnection();
			prepStat = conn.prepareStatement("UPDATE ORDERS SET " +
					"ORDER_ID=?, " +
					"CLIENT_PID=?, " +
					"CAR_ID=?, " +
					"MANAGER_NAME=?, " +
					"ORDER_STATE=?, " +
					"ORDER_EXPIRES=?, " +
					"ORDER_CREATED=?, " +
					"ORDER_REJECT_REASON=? "
					+ "WHERE ORDER_ID=?;");
			prepStat.setLong(1, o.getId());
			prepStat.setString(2, o.getClient().getPassportId());
			prepStat.setLong(3, o.getCar().getId());
			prepStat.setString(4, o.getManager().getName());
			prepStat.setString(5, String.valueOf(o.getState()));
			if(o.getOrderExpires()!=null){
				prepStat.setString(6, o.getOrderExpires());
			} else{
				prepStat.setString(6, null);
			}
			prepStat.setString(7, o.getOrderCreated());
			prepStat.setString(8, o.getRejectReason());
			prepStat.setLong(9, o.getId());
			return prepStat.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				prepStat.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return -1;
	}
	
	public int updateCar(Car c) {
		try {
			conn = getConnection();
//			prepStat = conn
//					.prepareStatement("UPDATE CLIENTS SET CLIENT_PID=?, "
//							+ "CLIENT_NAME=?, " + "CLIENT_MONEY=?, "
//							+ "CLIENT_PASSWORD=?, " + "CLIENT_EMAIL=? "
//							+ "WHERE CLIENT_PID=?;");
			prepStat = conn.prepareStatement("UPDATE CARS SET " +
					"CAR_ID=?," +
					"CAR_RENT_BY=?, " +
					"CAR_MODEL=?, " +
					"CAR_PRICE=?, " +
					"CAR_TYPE=?, " +
					"CAR_DAMAGES=? "
					+ "WHERE CAR_ID=?;");
			prepStat.setLong(1, c.getId());
			if (c.getRentBy()!=null){
				prepStat.setString(2, c.getRentBy().getPassportId());	
			}else{
				prepStat.setString(2, null);
			}
			prepStat.setString(3, c.getModel());
			prepStat.setDouble(4, c.getPrice());
			prepStat.setString(5, String.valueOf(c.getCarType()));
			prepStat.setString(6, String.valueOf(c.getDamages()));
			prepStat.setLong(7, c.getId());
			return prepStat.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				prepStat.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return -1;
	}
	
	public int updateManager(Manager m) {
		try {
			conn = getConnection();
//			prepStat = conn
//					.prepareStatement("UPDATE CLIENTS SET CLIENT_PID=?, "
//							+ "CLIENT_NAME=?, " + "CLIENT_MONEY=?, "
//							+ "CLIENT_PASSWORD=?, " + "CLIENT_EMAIL=? "
//							+ "WHERE CLIENT_PID=?;");
			prepStat = conn.prepareStatement("UPDATE MANAGERS SET " +
					"MANAGER_NAME=?," 
					+ "MANAGER_PASSWORD=? " 
					+ "WHERE MANAGER_NAME=?;");
			prepStat.setString(1, m.getName());
			prepStat.setString(2, m.getPassword());
			prepStat.setString(3, m.getName());
			return prepStat.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				prepStat.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return -1;
	}

	private void statementCreate() throws SQLException {
		conn = getConnection();
		myStat = conn.createStatement();
	}
	
	public ArrayList<Client> requestClients() {
		ArrayList<Client> clients = null;
		try {
			statementCreate();
			clients = new ArrayList<Client>();
			resultSet = myStat.executeQuery("SELECT * FROM CLIENTS");
			while (resultSet.next()) {
				// Client(String name, String passportId, float money,
				// String password, String email)
				try {
					clients.add(new Client(resultSet
							.getString("CLIENT_NAME"), resultSet.getString(
							"CLIENT_PID").toUpperCase(), resultSet
							.getFloat("CLIENT_MONEY"), resultSet
							.getString("CLIENT_PASSWORD"), resultSet
							.getString("CLIENT_EMAIL")));
				} catch (ClientException e) {
					System.out.println(e.getMessage());
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
		    try {
				myStat.close();
				conn.close();
				return clients;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}

	public synchronized ArrayList<Car> requestCars() {
		ArrayList<Car> cars = null;
		try {
			statementCreate();
			cars = new ArrayList<Car>();
			resultSet = myStat.executeQuery("SELECT * FROM CARS");
			while (resultSet.next()) {
				// Car(String aModel, double aPrice, String carType)
				try {
					cars.add(new Car(resultSet.getString("CAR_MODEL"),
							resultSet.getDouble("CAR_PRICE"), resultSet
									.getString("CAR_TYPE")));
					cars.get(cars.size() - 1).setDamages(Damages.valueOf(resultSet.getString("CAR_DAMAGES")));
					cars.get(cars.size() - 1).setRentBy(Management.getClientByPID(resultSet.getString("CAR_RENT_BY")));
					cars.get(cars.size() - 1).setCarId(resultSet.getLong("CAR_ID"));
				} catch (CarException e) {
					System.out.println(e.getMessage());
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
		    try {
		    	myStat.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return cars;
	}
	@Override
	public synchronized ArrayList<Car> requestFreeCars() {
		ArrayList<Car> cars = null;
		try {
			statementCreate();
			cars = new ArrayList<Car>();
			resultSet = myStat.executeQuery("SELECT * FROM CARS WHERE CAR_RENT_BY IS NULL");
			while (resultSet.next()) {
				// Car(String aModel, double aPrice, String carType)
				try {
					cars.add(new Car(resultSet.getString("CAR_MODEL"),
							resultSet.getDouble("CAR_PRICE"), resultSet
									.getString("CAR_TYPE")));
					cars.get(cars.size() - 1).setDamages(Damages.valueOf(resultSet.getString("CAR_DAMAGES")));
					cars.get(cars.size() - 1).setRentBy(Management.getClientByPID(resultSet.getString("CAR_RENT_BY")));
					cars.get(cars.size() - 1).setCarId(resultSet.getLong("CAR_ID"));
				} catch (CarException e) {
					System.out.println(e.getMessage());
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
		    try {
		    	myStat.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return cars;
	}
	
	public ArrayList<Manager> requestManagers() {
		ArrayList<Manager> managers = null;
		try {
			statementCreate();
			resultSet = myStat.executeQuery("SELECT * FROM MANAGERS");
			managers = new ArrayList<Manager>();
			while (resultSet.next()) {
				// Car(String aModel, double aPrice, String carType)
				try {
					managers.add(new Manager(resultSet
							.getString("MANAGER_NAME"), resultSet
							.getString("MANAGER_PASSWORD")));
				} catch (SQLException e) {
					System.out.println(e.getMessage());
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
		    try {
		    	myStat.close();
				conn.close();
				return managers;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}
	public ArrayList<Order> requestOrders() {
		ArrayList<Order> orders = null;
		try {
//			statementCreate();
			Connection conn = null;
			Statement myStat = null;
			conn = getConnection();
			myStat = conn.createStatement();
//			System.out.println(myStat);
			resultSet = myStat.executeQuery("SELECT * FROM ORDERS");
			orders = new ArrayList<Order>();
			while (resultSet.next()) {
				// Order(Client client, Car car, int days)
				try {
					orders.add(new Order(DbWriter.getManagerById(
							resultSet.getString("MANAGER_NAME")),
							DbWriter.getClientById(resultSet.getString("CLIENT_PID")), 
							DbWriter.getCarById(resultSet.getLong("CAR_ID")),
							resultSet.getString("ORDER_CREATED")));
					
					orders.get(orders.size() - 1).setId(
							resultSet.getLong("ORDER_ID"));
					orders.get(orders.size() - 1).setState(
							OrderState.valueOf(resultSet
									.getString("ORDER_STATE")));
					orders.get(orders.size() - 1).setOrderExpires(
							resultSet.getString("ORDER_EXPIRES"));
					orders.get(orders.size() - 1).setRejectReason(
							resultSet.getString("ORDER_REJECT_REASON"));
				} catch (SQLException e) {
					e.printStackTrace();
					System.out.println(e.getMessage());
//				} catch (NoSuchManagerException e) {
//					e.printStackTrace();
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
		    try {
		    	myStat.close();
				conn.close();
				return orders;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}
	
	public void storeOrders() {
			ArrayList<Order> orders = Management.getOrders();
			for (Order o : orders) {
				createOrder(o);
			}
			System.out.println("Orders are stored in database.\n");
	}

	public void storeManagers() {
			ArrayList<Manager> managers = Management.getManagers();
			for (Manager m : managers) {
				createManager(m);
			}
			System.out.println("Managers are stored in database.\n");
	}

	public void storeCars() {
			ArrayList<Car> cars = Management.getCars();
			for (Car c : cars) {
				createCar(c);
			}
			System.out.println("Cars are stored in database.\n");
	}

	public void storeClients() {
			ArrayList<Client> clients = Management.getClients();
			for (Client c : clients) {
				createClient(c);
			}
			System.out.println("Clients are stored in database.\n");
	}


	public int deleteClient(Client c) {
		try {
			conn = getConnection();
			prepStat = conn.prepareStatement("DELETE FROM CLIENTS WHERE CLIENT_PID=?;");
			prepStat.setString(1, c.getPassportId());
			return prepStat.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
		    try {
				prepStat.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return -1;
	}

	public int deleteClient(String cpid) {
		try {
			conn = getConnection();
			prepStat = conn.prepareStatement("DELETE FROM CLIENTS WHERE CLIENT_PID=?;");
			prepStat.setString(1, cpid);
			return prepStat.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
		    try {
				prepStat.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return -1;
	}


	public int deleteOrder(Order o) {
		try {
			conn = getConnection();
			prepStat = conn.prepareStatement("DELETE FROM ORDERS WHERE ORDER_ID=?;");
			prepStat.setLong(1, o.getId());
			return prepStat.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
		    try {
				prepStat.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return -1;
	}
	

	public int deleteOrder(Long oid) {
		try {
			conn = getConnection();
			prepStat = conn.prepareStatement("DELETE FROM ORDERS WHERE ORDER_ID=?;");
			prepStat.setLong(1, oid);
			return prepStat.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
		    try {
				prepStat.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return -1;
	}

	public int deleteCar(Car c) {
		try {
			conn = getConnection();
			prepStat = conn.prepareStatement("DELETE FROM CARS WHERE CAR_ID=?;");
			prepStat.setLong(1, c.getId());
			return prepStat.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
		    try {
				prepStat.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return -1;
	}
	
	public int deleteCar(Long cid) {
		try {
			conn = getConnection();
			prepStat = conn.prepareStatement("DELETE FROM CARS WHERE CAR_ID=?;");
			prepStat.setLong(1, cid);
			return prepStat.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
		    try {
				prepStat.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return -1;
	}

	public int deleteManager(Manager m) {
		try {
			conn = getConnection();
			prepStat = conn.prepareStatement("DELETE FROM MANAGERS WHERE MANAGER_NAME=?;");
			prepStat.setString(1, m.getName());
			return prepStat.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
		    try {
				prepStat.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return -1;
	}
	public int deleteManager(String mname) {
		try {
			conn = getConnection();
			prepStat = conn.prepareStatement("DELETE FROM MANAGERS WHERE MANAGER_NAME=?;");
			prepStat.setString(1, mname);
			return prepStat.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
		    try {
				prepStat.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return -1;
	}

	@Override
	public Client getClientById(String id) {
		Client client = null;
		Connection conn = null;
		Statement myStat = null;
		ResultSet resultSet = null;
		try {
			conn = getConnection();
			myStat = conn.createStatement();
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		try {
			statementCreate();
			// clients = new ArrayList<Client>();
			resultSet = myStat.executeQuery("SELECT * FROM CLIENTS WHERE CLIENT_PID='"+id+"'");
			while (resultSet.next()) {
				// Client(String name, String passportId, float money,
				// String password, String email)
				try {
					client = new Client(resultSet
							.getString("CLIENT_NAME"), resultSet.getString(
							"CLIENT_PID").toUpperCase(), resultSet
							.getFloat("CLIENT_MONEY"), resultSet
							.getString("CLIENT_PASSWORD"), resultSet
							.getString("CLIENT_EMAIL"));
				} catch (ClientException e) {
					System.out.println(e.getMessage());
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
		    try {

			    	myStat.close();
					conn.close();

			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return client;
	}

	@Override
	public Car getCarById(Long id) {
		Car car = null;
		Connection conn = null;
		Statement myStat = null;
		ResultSet resultSet = null;
		try {
			conn = getConnection();
			myStat = conn.createStatement();
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		try {
			statementCreate();
			resultSet = myStat.executeQuery("SELECT * FROM CARS WHERE CAR_ID="+id);
			while (resultSet.next()) {
					try {
						car = new Car(resultSet.getString("CAR_MODEL"),
								resultSet.getDouble("CAR_PRICE"), resultSet
								.getString("CAR_TYPE"));
						
						car.setDamages(Damages.valueOf(resultSet.getString("CAR_DAMAGES")));
						car.setRentBy(DbWriter.getClientById(resultSet.getString("CAR_RENT_BY")));
						car.setCarId(id);
					} catch (CarException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
			}
		}catch (SQLException s){
			s.printStackTrace();
		} finally {
		    try {
				
			    	myStat.close();
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return car;
	}
	
//	@Override
//	public synchronized ArrayList<Car> getAllCars() {
//		ArrayList<Car> cars = new ArrayList<Car>();
//		try {
//			statementCreate();
//			
//			resultSet = myStat.executeQuery("SELECT * FROM CARS");
//			
//			while (resultSet.next()) {
//					try {
//						cars.add(new Car(resultSet.getString("CAR_MODEL"),
//								resultSet.getDouble("CAR_PRICE"), resultSet.getString("CAR_TYPE")));
//						
//						cars.get(cars.size()-1).setDamages(Damages.valueOf(resultSet.getString("CAR_DAMAGES")));
//						cars.get(cars.size()-1).setRentBy(DbWriter.getClientById(resultSet.getString("CAR_RENT_BY")));
//						cars.get(cars.size()-1).setCarId(resultSet.getLong("CAR_ID"));
//						
//					} catch (CarException e) {
//						// TODO Auto-generated catch block
//						e.printStackTrace();
//					}
//			}
//			
//		}catch (SQLException s){
//			s.printStackTrace();
//		} finally {
//		    try {
//				myStat.close();
//				conn.close();
//				return cars;
//			} catch (SQLException e) {
//				e.printStackTrace();
//			}
//		}
//		return null;
//	}

	@Override
	public Order getOrderById(Long id) {
		Order order = null;
		Connection conn = null;
		Statement myStat = null;
		ResultSet resultSet = null;
		try {
			conn = getConnection();
			myStat = conn.createStatement();
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		try {
			statementCreate();
			resultSet = myStat.executeQuery("SELECT * FROM ORDERS WHERE ORDER_ID="+id);
			while (resultSet.next()) {
				order = new Order(DbWriter.getManagerById(resultSet.getString("MANAGER_NAME")),
								DbWriter.getClientById(resultSet.getString("CLIENT_PID")),
								DbWriter.getCarById(resultSet.getLong("CAR_ID")),
								resultSet.getString("ORDER_CREATED"));
				order.setId(resultSet.getLong("ORDER_ID"));		
				order.setState(OrderState.valueOf(resultSet.getString("ORDER_STATE")));
				order.setOrderExpires(resultSet.getString("ORDER_EXPIRES"));
				order.setRejectReason(resultSet.getString("ORDER_REJECT_REASON"));
			}
		}catch (SQLException s){
			s.printStackTrace();
		} finally {
		    try {
				
			    	myStat.close();
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return order;
	}

	@Override
	public Manager getManagerById(String id) {
		Manager manager = null;
		Connection conn = null;
		Statement myStat = null;
		ResultSet resultSet = null;
		try {
			conn = getConnection();
			myStat = conn.createStatement();
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		try {
			// managers = new ArrayList<Manager>();
//			System.out.println(myStat);
			resultSet = myStat.executeQuery("SELECT * FROM MANAGERS WHERE MANAGER_NAME='"+id+"'");
			while (resultSet.next()) {
				manager = new Manager(resultSet.getString("MANAGER_NAME"), resultSet.getString("MANAGER_PASSWORD"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
		    try {

			    	myStat.close();
					conn.close();

			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return manager;
	}

	@Override
	public ArrayList<Order> getOrdersForClient(String id) {
		ArrayList<Order> orders = null;
		try {
//			statementCreate();
			Connection conn = null;
			Statement myStat = null;
			conn = getConnection();
			myStat = conn.createStatement();
//			System.out.println(myStat);
			resultSet = myStat.executeQuery("SELECT * FROM ORDERS WHERE CLIENT_PID='"+id+"'");
			orders = new ArrayList<Order>();
			while (resultSet.next()) {
				// Order(Client client, Car car, int days)
				try {
					orders.add(new Order(DbWriter.getManagerById(
							resultSet.getString("MANAGER_NAME")),
							DbWriter.getClientById(resultSet.getString("CLIENT_PID")), 
							DbWriter.getCarById(resultSet.getLong("CAR_ID")),
							resultSet.getString("ORDER_CREATED")));
					
					orders.get(orders.size() - 1).setId(
							resultSet.getLong("ORDER_ID"));
					orders.get(orders.size() - 1).setState(
							OrderState.valueOf(resultSet
									.getString("ORDER_STATE")));
					orders.get(orders.size() - 1).setOrderExpires(
							resultSet.getString("ORDER_EXPIRES"));
					orders.get(orders.size() - 1).setRejectReason(
							resultSet.getString("ORDER_REJECT_REASON"));
				} catch (SQLException e) {
					e.printStackTrace();
					System.out.println(e.getMessage());
//				} catch (NoSuchManagerException e) {
//					e.printStackTrace();
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
		    try {
		    	myStat.close();
				conn.close();
				return orders;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}

}
